from openpyxl import load_workbook
from operator import is_not
from functools import partial
from itertools import takewhile, islice
import re
import sqlite3


def parse_excel(excel_file, query_sql_file):
    wb = load_workbook(excel_file, data_only=True)
    ws = wb.active

    db = sqlite3.connect(':memory:')
    cur = db.cursor()
    cur.row_factory = sqlite3.Row

    # 建表
    fields = list(map(partial(re.sub, r'[()、]', ''),
                      takewhile(partial(is_not, None), next(ws.values))))
    cur.execute('CREATE TABLE INVOICE_BOOK ({})'.format(
        ','.join([f'{field} TEXT' for field in fields])))

    # 灌数
    data = list(islice(ws.values, 1, None))
    cur.executemany('INSERT INTO INVOICE_BOOK VALUES ({})'.format(
        ','.join(['?'] * len(fields))), data)

    # 分组 SQL 查询
    cur.execute(open(query_sql_file, encoding='utf-8').read())

    for row in cur.fetchall():
        print('取得{销方名称}{发票类型}{发票数量}份，{发票代码号码}，'
              '金额共计{金额:,.2f}元，税额合计{税额:,.2f}元，'
              '价税合计{价税合计:,.2f}元。'.format_map(row))


parse_excel(r'./data.xlsx', r'./query.sql')
